/**********************************************************************
 * Project : WoWGuildRoster (http://www.codeplex.com/WowGuildRoster/)
 * Module  : DotNetWarcraft.WoWGuildRoster.Business.GuildRosterController
 **********************************************************************
 * 
 * 
 * 
 **********************************************************************
 * License : All code is subject to licensing terms per the Microsoft
 *   Permissive License (Ms-PL) v1.1. For full license information,
 *   refer to the packaged license.txt file.
 **********************************************************************
 * Revision History:
 * -----------------
 * 2007-07-29 -- Cliff Simpkins: Initial Version
 * 2008-01-20 -- Cliff Simpkins: Created local DataSet
 * 2008-12-24 -- Cliff Simpkins: Added Count entries to Guild Entries methods
 **********************************************************************
 */
using System;
using System.Collections.Generic;
using System.Data;
using System.Configuration;

using DotNetNuke.Framework.Providers;
using DotNetWarcraft.Common;
using DotNetWarcraft.WoWGuildRoster.Business;


using Microsoft.ApplicationBlocks.Data;

namespace DotNetWarcraft.WoWGuildRoster.Business {
  public class DnnGuildRosterController : GuildRosterController {
    public static String VersionNumber {
      get {
        return WGRDnnConstants.DNW_WGR_DNN_Version;
      }
    }
    public static String ApplicationName {
      get {
        return WGRDnnConstants.DNW_WGR_DNN_AppName;
      }
    }
    public static Boolean LoadInProgress {
      get { return ProtectLocalCache; }
      set { ProtectLocalCache = value; }
    }

    protected static DataTable LocalRosterToDnnModuleMapping {
      get {
        return LocalWgrDataCache.Tables[4];
      }
    }
    protected static DataTable LocalCharEntryToDnnUserMapping {
      get {
        return LocalWgrDataCache.Tables[5];
      }
    }


    public new static Data.WGR_DnnSqlDataProvider DbProvider {
      get {
        if (_dbProvider == null || !(_dbProvider is Data.WGR_DnnSqlDataProvider)) {
          _dbProvider = Data.WGR_DnnSqlDataProvider.Instance();
        }
        
        return (Data.WGR_DnnSqlDataProvider)_dbProvider;
      }
    }

    #region "Internal Static Methods - Database interactions"
    protected new static void DB_LoadLocalData() {
      GuildRosterController.DB_LoadLocalData();
      DB_LayerDnnPrimaryKeys();
      DB_LayerDnnRelationships();
    }

    protected static void DB_LayerDnnPrimaryKeys() {

      LocalRosterToDnnModuleMapping.PrimaryKey = new DataColumn[] {
        LocalRosterToDnnModuleMapping.Columns["module_id"], 
        LocalRosterToDnnModuleMapping.Columns["portal_id"], 
        LocalRosterToDnnModuleMapping.Columns["roster_id"]
      };
      LocalCharEntryToDnnUserMapping.PrimaryKey = new DataColumn[] {
        LocalCharEntryToDnnUserMapping.Columns["user_id"], 
        LocalCharEntryToDnnUserMapping.Columns["portal_id"], 
        LocalCharEntryToDnnUserMapping.Columns["char_id"]
      };

    }
    protected static void DB_LayerDnnRelationships() {
      if (LocalWgrDataCache.Relations.Contains("DnnModuleRosters")) {
        LocalWgrDataCache.Relations.Remove("DnnModuleRosters");
      }
      if (!LocalWgrDataCache.Relations.Contains("DnnModuleRosters")) {
        DataRelation _rel1 = new DataRelation("DnnModuleRosters",
                                 LocalRosterToDnnModuleMapping.Columns["roster_id"],
                                 LocalGuildRosters.Columns["roster_id"]);
        LocalWgrDataCache.Relations.Add(_rel1);
      }

      if (LocalWgrDataCache.Relations.Contains("DnnUserCharEntries")) {
        LocalWgrDataCache.Relations.Remove("DnnUserCharEntries");
      }
      if (!LocalWgrDataCache.Relations.Contains("DnnUserCharEntries")) {
        DataRelation _rel2 = new DataRelation("DnnUserCharEntries",
                           LocalCharEntryToDnnUserMapping.Columns["char_id"],
                           LocalGuildCharEntries.Columns["roster_id"]);
        LocalWgrDataCache.Relations.Add(_rel2);
      }
    }

    #endregion

    static DnnGuildRosterController() {
      DnnGuildRosterController.SetConnectionString(DbProvider.ConnectionString);
      DnnGuildRosterController.SetDatabaseOwner(DbProvider.DatabaseOwner);
      DnnGuildRosterController.SetObjectQualifier(DbProvider.ObjectQualifier);
      DnnGuildRosterController.SetProviderPath(DbProvider.ProviderPath);
    }

    #region Static Methods - Guild Roster Modules
    public static List<GuildRosterModule> GetRostersByModule(int ModuleId, int PortalID) {
      //return DbProvider.GetRosterModulesByModule(ModuleId, PortalID);
      DataRow[] _modRosters = LocalRosterToDnnModuleMapping.Select("[module_id] = " + ModuleId + " AND [portal_id] = " + PortalID);

      List<GuildRosterModule> _rosterMods = new List<GuildRosterModule>();
      foreach (DataRow _dr in _modRosters) {
        _rosterMods.Add(new GuildRosterModule(_dr));
      }
      return _rosterMods;
    }
    public static List<GuildRosterModule> GetModulesByRoster(int RosterId) {
      //return DbProvider.GetRosterModulesByRoster(RosterId);
      DataRow[] _modRosters = LocalRosterToDnnModuleMapping.Select("roster_id = " + RosterId.ToString());

      List<GuildRosterModule> _rosterMods = new List<GuildRosterModule>();
      foreach (DataRow _dr in _modRosters) {
        _rosterMods.Add(new GuildRosterModule(_dr));
      }
      return _rosterMods;
    }

    public static void AddRosterModuleEntry(GuildRosterModule objGuildRosterModule) {
      DbProvider.AddRosterModuleEntry(objGuildRosterModule);
      DB_ClearLocalData();
    }

    public static void DeleteRosterModuleEntries(int ModuleId, int PortalID) {
      DbProvider.DeleteRosterModulesByModule(ModuleId, PortalID);
      DB_ClearLocalData();
    }
    public static void DeleteRosterModuleEntry(int ModuleId, int PortalID, int RosterID) {
      DbProvider.DeleteRosterModulesByIDs(ModuleId, PortalID, RosterID);
      DB_ClearLocalData();
    }
    public static void DeleteRosterModuleEntriesByRosterID(int RosterID) {
      DbProvider.DeleteRosterModulesByRoster(RosterID);
      DB_ClearLocalData();
    }

    protected static String RosterIDsFromRosterModuleList(List<GuildRosterModule> RosterModules) {
      String _rosterModIDs = "";

      foreach (GuildRosterModule _rosMod in RosterModules) {
        if (_rosterModIDs.Trim().Length > 0) { _rosterModIDs += ", "; }
        _rosterModIDs += _rosMod.Roster_ID.ToString();
      }
      return _rosterModIDs;
    }

    #endregion
    #region Static Methods - Char Users
    public static List<CharEntryUser> GetCharUsersByUser(int UserId, int PortalID) {
      //return DbProvider.GetCharUsersByUser(UserId, PortalID);
      DataRow[] _charUsers = LocalCharEntryToDnnUserMapping.Select("[user_id] = " + UserId + " AND [portal_id] = " + PortalID);

      List<CharEntryUser> _chars = new List<CharEntryUser>();
      foreach (DataRow _dr in _charUsers) {
        _chars.Add(new CharEntryUser(_dr));
      }
      return _chars;

    }
    public static List<CharEntryUser> GetCharUsersByCharEntry(int CharId) {
      //return DbProvider.GetCharUsersByCharEntry(CharId);
      DataRow[] _charUsers = LocalCharEntryToDnnUserMapping.Select("[char_id] = " + CharId);

      List<CharEntryUser> _chars = new List<CharEntryUser>();
      foreach (DataRow _dr in _charUsers) {
        _chars.Add(new CharEntryUser(_dr));
      }
      return _chars;

    }

    public static void AddCharUserEntry(CharEntryUser objCharEntryUser) {
      DbProvider.DeleteCharUserEntriesByCharEntry(objCharEntryUser.CharID, objCharEntryUser.PortalID);
      DbProvider.AddCharUserEntry(objCharEntryUser);

      GuildEntry _charEntry = DnnGuildRosterController.GetCharEntry(objCharEntryUser.CharID);

      if (_charEntry.Primary_Char_Flag > 0) {
        List<GuildEntry> _alts = _charEntry.AltCharacters;

        foreach (GuildEntry _alt in _alts) {
          CharEntryUser _mapping = new CharEntryUser(_alt.Char_ID, objCharEntryUser.PortalID, objCharEntryUser.UserID);
          DnnGuildRosterController.AddCharUserEntry(_mapping);
        }
      }
      DB_ClearLocalData();
    }
    public static void AdjustMainCharAcrossUserEntries(int UserId, int PortalID, int MainCharID) {

      List<CharEntryUser> _userEntries = DnnGuildRosterController.GetCharUsersByUser(UserId, PortalID);

      foreach (CharEntryUser _userEntry in _userEntries) {
        if (_userEntry.CharID > 0) {
          GuildEntry _char = DnnGuildRosterController.GetCharEntry(_userEntry.CharID);

          if (_char.Char_ID == MainCharID) {
            _char.Primary_Char_Flag = 1;
            _char.Alt_to_Main_ID = -1;
          } else {
            _char.Primary_Char_Flag = 0;
            _char.Alt_to_Main_ID = MainCharID;
          }

          if (_char.Char_ID > 0) {
            DnnGuildRosterController.UpdateCharEntry(_char);
          }
        }
      }
      DB_ClearLocalData();
    }

    public static void DeleteCharUserEntriesByUser(int UserId, int PortalId) {
      DbProvider.DeleteCharUserEntriesByUser(UserId, PortalId);
      DB_ClearLocalData();

    }
    public static void DeleteCharUserEntriesByIDs(int UserId, int PortalId, int CharEntryId) {
      DbProvider.DeleteCharUserEntriesByIDs(UserId, PortalId, CharEntryId);
      DB_ClearLocalData();

    }
    public static void DeleteCharUserEntriesByCharEntry(int CharEntryId) {
      DbProvider.DeleteCharUserEntriesByCharEntry(CharEntryId);
      DB_ClearLocalData();

    }
    public static void DeleteCharUserEntriesByCharEntry(int CharEntryId, int PortalId) {
      DbProvider.DeleteCharUserEntriesByCharEntry(CharEntryId, PortalId);
      DB_ClearLocalData();

    }

    #endregion
    #region Static Methods - Guild Rosters
    public static List<GuildRoster> GetRosters(int moduleID, int PortalID) {
      List<GuildRosterModule> _rosterMods = GetRostersByModule(moduleID, PortalID);
      List<GuildRoster> _guilds = new List<GuildRoster>();

      foreach (GuildRosterModule _grm in _rosterMods) {
        try {
          _guilds.Add(DnnGuildRosterController.GetRoster(_grm.Roster_ID));
          } catch (DotNetWarcraft.WoWGuildRoster.Business.Exceptions.RosterRowInvalid e) {
            //If a guild roster had been deleted that previously existed, this
            //  error will get thrown - so we catch it, log it, and move along
            e.WriteExceptionToDB();
            //And let's clean up after ourselves
            DnnGuildRosterController.DeleteRosterModuleEntry(moduleID, PortalID, _grm.Roster_ID);
            }
        }

      return _guilds;
      }
    #endregion
    #region Static Methods - Guild Entries
    public static List<GuildEntry> GetCharEntriesByClass(int moduleID, int PortalID, String ClassName, bool mainsOnly) {
      //return DbProvider.GetCharEntriesByClass(moduleID, ClassName, mainsOnly);
      List<GuildRosterModule> _rosterMods = GetRostersByModule(moduleID, PortalID);
      List<GuildEntry> _chars = new List<GuildEntry>();

      if (_rosterMods.Count > 0) {
        String _rosterIDs = RosterIDsFromRosterModuleList(_rosterMods);

        String _filter = "[roster_id] in (" + _rosterIDs.ToString() + ") AND [char_class] = '" + ClassName + "'";
        if (mainsOnly) { _filter += " AND [primary_char] = 1"; }
        if (true) { // For now, we'll return only current characters through DNN interface
          _filter += " AND [current_flag] = 1";
        }

        DataRow[] _modRosters = LocalGuildCharEntries.Select(_filter, "[char_level] DESC, [char_name] ASC");

        foreach (DataRow _dr in _modRosters) {
          _chars.Add(new GuildEntry(_dr));
        }
      }

      return _chars;
    }
    public static int GetCharEntriesByClass_Count(int moduleID, int PortalID, String ClassName, bool mainsOnly) {
      //return DbProvider.GetCharEntriesByClass(moduleID, ClassName, mainsOnly);
      List<GuildRosterModule> _rosterMods = GetRostersByModule(moduleID, PortalID);
      int _charCount = 0;

      if (_rosterMods.Count > 0) {
        String _rosterIDs = RosterIDsFromRosterModuleList(_rosterMods);

        String _filter = "[roster_id] in (" + _rosterIDs.ToString() + ") AND [char_class] = '" + ClassName + "'";
        if (mainsOnly) { _filter += " AND [primary_char] = 1"; }
        if (true) { // For now, we'll return only current characters through DNN interface
          _filter += " AND [current_flag] = 1";
          }
        DataRow[] _modRosters = LocalGuildCharEntries.Select(_filter, "[char_level] DESC, [char_name] ASC");

        _charCount = _modRosters.Length;
        }

      return _charCount;
      }

    public static List<GuildEntry> GetCharEntriesByLevel(int moduleID, int PortalID, int LevelMax, int LevelMin, bool mainsOnly) {
      return GetCharEntriesByLevel(moduleID, PortalID, LevelMax, LevelMin, mainsOnly, true);
    }
    public static List<GuildEntry> GetCharEntriesByLevel(int moduleID, int PortalID, int LevelMax, int LevelMin, bool mainsOnly, bool sortByLevel) {
      //return DbProvider.GetCharEntriesByLevel(moduleID, LevelMax, LevelMin, mainsOnly);
      List<GuildRosterModule> _rosterMods = GetRostersByModule(moduleID, PortalID);
      List<GuildEntry> _chars = new List<GuildEntry>();

      if (_rosterMods.Count > 0) {
        String _rosterIDs = RosterIDsFromRosterModuleList(_rosterMods);

        String _filter = "[roster_id] in (" + _rosterIDs.ToString() + ") AND [char_level] >= " + LevelMin.ToString() + " AND [char_level] <= " + LevelMax.ToString();
        if (mainsOnly) { _filter += " AND [primary_char] = 1"; }
        if (true) { // For now, we'll return only current characters through DNN interface
          _filter += " AND [current_flag] = 1";
        }

        DataRow[] _modRosters;
        if (sortByLevel) {
          _modRosters = LocalGuildCharEntries.Select(_filter, "[char_level] DESC, [char_name] ASC");
        } else {
          _modRosters = LocalGuildCharEntries.Select(_filter, "[char_name] ASC");
        }

        foreach (DataRow _dr in _modRosters) {
          _chars.Add(new GuildEntry(_dr));
        }
      }

      return _chars;
    }
    public static int GetCharEntriesByLevel_Count(int moduleID, int PortalID, int LevelMax, int LevelMin, bool mainsOnly) {
      List<GuildRosterModule> _rosterMods = GetRostersByModule(moduleID, PortalID);
      int _charCount = 0;

      if (_rosterMods.Count > 0) {
        String _rosterIDs = RosterIDsFromRosterModuleList(_rosterMods);

        String _filter = "[roster_id] in (" + _rosterIDs.ToString() + ") AND [char_level] >= " + LevelMin.ToString() + " AND [char_level] <= " + LevelMax.ToString();
        if (mainsOnly) { _filter += " AND [primary_char] = 1"; }
        if (true) { // For now, we'll return only current characters through DNN interface
          _filter += " AND [current_flag] = 1";
          }

        DataRow[] _modRosters = LocalGuildCharEntries.Select(_filter, "[char_level] DESC, [char_name] ASC");

        _charCount = _modRosters.Length;
        }
      return _charCount;
      }

    public static List<GuildEntry> GetCharEntriesByGuildRank(int moduleID, int portalID, int guildRank, bool mainsOnly) {
      return GetCharEntriesByGuildRank(moduleID, portalID, guildRank, guildRank, mainsOnly);
      }
    public static List<GuildEntry> GetCharEntriesByGuildRank(int moduleID, int PortalID, int minGuildRank, int maxGuildRank, bool mainsOnly) {

      List<GuildRosterModule> _rosterMods = GetRostersByModule(moduleID, PortalID);
      List<GuildEntry> _chars = new List<GuildEntry>();

      if (_rosterMods.Count > 0) {
        String _rosterIDs = RosterIDsFromRosterModuleList(_rosterMods);

        String _filter = "[roster_id] in (" + _rosterIDs.ToString() + ") AND [game_guild_rank] >= " + minGuildRank.ToString() + " AND [game_guild_rank] <= " + maxGuildRank.ToString();
        if (mainsOnly) { _filter += " AND [primary_char] = 1"; }
        if (true) { // For now, we'll return only current characters through DNN interface
          _filter += " AND [current_flag] = 1";
          }

        DataRow[] _modRosters = LocalGuildCharEntries.Select(_filter, "[game_guild_rank] ASC, [char_level] DESC, [char_name] ASC");

        foreach (DataRow _dr in _modRosters) {
          _chars.Add(new GuildEntry(_dr));
          }
        }

      return _chars;
      }
    public static int GetCharEntriesByGuildRank_Count(int moduleID, int portalID, int guildRank, bool mainsOnly) {
      return GetCharEntriesByGuildRank_Count(moduleID, portalID, guildRank, guildRank, mainsOnly);
      }
    public static int GetCharEntriesByGuildRank_Count(int moduleID, int PortalID, int minGuildRank, int maxGuildRank, bool mainsOnly) {
      List<GuildRosterModule> _rosterMods = GetRostersByModule(moduleID, PortalID);
      int _charCount = 0;

      if (_rosterMods.Count > 0) {
        String _rosterIDs = RosterIDsFromRosterModuleList(_rosterMods);

        String _filter = "[roster_id] in (" + _rosterIDs.ToString() + ") AND [game_guild_rank] >= " + minGuildRank.ToString() + " AND [game_guild_rank] <= " + maxGuildRank.ToString();
        if (mainsOnly) { _filter += " AND [primary_char] = 1"; }
        if (true) { // For now, we'll return only current characters through DNN interface
          _filter += " AND [current_flag] = 1";
          }

        DataRow[] _modRosters = LocalGuildCharEntries.Select(_filter, "[game_guild_rank] ASC, [char_level] DESC, [char_name] ASC");

        _charCount = _modRosters.Length;
        }
      return _charCount;
      }
    #endregion
    #region Static Methods - Guild Entry Stats
    public static List<QuickStatListItem> GetEntryStatsLIs_ByProfession(int moduleID, int PortalID, String ProfessionName, bool mainsOnly) {
      //return DbProvider.GetCharEntriesByProfession(moduleID, ProfessionName, mainsOnly);
      List<GuildRosterModule> _rosterMods = GetRostersByModule(moduleID, PortalID);
      List<QuickStatListItem> _chars = new List<QuickStatListItem>();

      if (_rosterMods.Count > 0) {
        String _rosterIDs = RosterIDsFromRosterModuleList(_rosterMods);

        String _filter = "[roster_id] in (" + _rosterIDs.ToString() + ") AND [stat_entry] = '" + ProfessionName + "'";
        if (mainsOnly) {
          _filter += " AND [primary_char] = 1";
        }
        if (true) { // For now, we'll return only current characters through DNN interface
          _filter += " AND [current_flag] = 1";
        }
        _chars = Helper_ProcessQStatLIs(LocalCharProfessions.Select(_filter, "[stat_value_int] DESC, [char_name] ASC"));
      }
      return _chars;

    }
    public static int GetEntryStatsLIs_ByProfession_Count(int moduleID, int PortalID, String ProfessionName, bool mainsOnly) {
      //return DbProvider.GetCharEntriesByProfession(moduleID, ProfessionName, mainsOnly);
      List<GuildRosterModule> _rosterMods = GetRostersByModule(moduleID, PortalID);
      int _charCount = 0;

      if (_rosterMods.Count > 0) {
        String _rosterIDs = RosterIDsFromRosterModuleList(_rosterMods);

        String _filter = "[roster_id] in (" + _rosterIDs.ToString() + ") AND [stat_entry] = '" + ProfessionName + "'";
        if (mainsOnly) {
          _filter += " AND [primary_char] = 1";
          }
        if (true) { // For now, we'll return only current characters through DNN interface
          _filter += " AND [current_flag] = 1";
          }
        _charCount = LocalCharProfessions.Select(_filter, "[stat_value_int] DESC, [char_name] ASC").Length;
        }
      return _charCount;

      }

    public static List<QuickStatListItem> GetEntryStatsLIs_ByStatEntryAndType(int moduleID, int PortalID, String statEntry, WarcraftStatTypes statTypeEnum, bool mainsOnly) {
      return DbProvider.GetEntryStatsLIs_ByStatEntryAndType(moduleID, statEntry, statTypeEnum, mainsOnly);

    }
    public static List<QuickStatListItem> GetEntryStatsLIs_ByStatEntryAndType(int moduleID, int PortalID, String statEntry, int statTypeID, bool mainsOnly) {
      return DbProvider.GetEntryStatsLIs_ByStatEntryAndType(moduleID, statEntry, statTypeID, mainsOnly);
    }
    #endregion

    #region Static Methods - Drop-Down Values
    public static List<DropDownVal> GetDropDown_DnnUsers(int portalID) {
      return DbProvider.GetDropDowns_DnnUsers(portalID);
    }
    public static List<DropDownVal> GetDropDown_DnnUsers(int portalID, String userName, int count) {
      return DbProvider.GetDropDowns_DnnUsers_Partial(portalID, userName, count);
    }

    public static List<DropDownVal> GetDropDowns_MainChars(int moduleID) {
      return DbProvider.GetDropDowns_MainChars(moduleID);
    }
    public static List<DropDownVal> GetDropDowns_MainChars(int moduleID, String charName, int count) {
      return DbProvider.GetDropDowns_MainChars_Partial(moduleID, charName, count);
    }
    #endregion
  }
}
